--  
-- Script to Create dbo.NotarialFormSerialNumber in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.NotarialFormSerialNumber Table'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO

SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE [dbo].[NotarialFormSerialNumber] (
   [Id] [int] IDENTITY (1, 1) NOT NULL,
   [Name] [nchar] (100) COLLATE Cyrillic_General_CI_AS NOT NULL
)
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   ALTER TABLE [dbo].[NotarialFormSerialNumber] ADD CONSTRAINT [PK_NotarialFormSerialNumber] PRIMARY KEY CLUSTERED ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.NotarialFormSerialNumber Table Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.NotarialFormSerialNumber Table'
END
GO


--  
-- Script to Create dbo.NotarialFormRange in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.NotarialFormRange Table'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO

SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE [dbo].[NotarialFormRange] (
   [Id] [int] IDENTITY (1, 1) NOT NULL,
   [Name] [nchar] (500) COLLATE Cyrillic_General_CI_AS NOT NULL,
   [StartNumber] [bigint] NOT NULL,
   [EndNumber] [bigint] NOT NULL,
   [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NotarialFormRange_CreateDate] DEFAULT (getdate()),
   [SerialNumberId] [int] NOT NULL CONSTRAINT [DF_NotarialFormRange_SerialNumberId] DEFAULT ((1))
)
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   ALTER TABLE [dbo].[NotarialFormRange] ADD CONSTRAINT [PK_NotarialFormNumberRange] PRIMARY KEY CLUSTERED ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.NotarialFormRange Table Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.NotarialFormRange Table'
END
GO


--  
-- Script to Create dbo.NotarialFormStatus in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.NotarialFormStatus Table'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO

SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE [dbo].[NotarialFormStatus] (
   [Id] [int] IDENTITY (1, 1) NOT NULL,
   [Alias] [nchar] (200) COLLATE Cyrillic_General_CI_AS NULL,
   [Name] [nvarchar] (500) COLLATE Cyrillic_General_CI_AS NOT NULL,
   [IsInvalid] [bit] NOT NULL CONSTRAINT [DF_NotarialFormStatus_IsInvalid] DEFAULT ((0))
)
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   ALTER TABLE [dbo].[NotarialFormStatus] ADD CONSTRAINT [PK_NotarialFormNumberStatus] PRIMARY KEY CLUSTERED ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.NotarialFormStatus Table Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.NotarialFormStatus Table'
END
GO


--  
-- Script to Create dbo.NotarialForm in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.NotarialForm Table'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO

SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE [dbo].[NotarialForm] (
   [Id] [int] IDENTITY (1, 1) NOT NULL,
   [Name] [bigint] NOT NULL,
   [OrderId] [int] NULL,
   [StatusId] [int] NOT NULL,
   [RangeId] [int] NOT NULL,
   [SerialNumberId] [int] NOT NULL CONSTRAINT [DF_NotarialForm_SerialNumberId] DEFAULT ((1)),
   [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NotarialForm_CreateDate] DEFAULT (getdate()),
   [EndDate] [datetime] NULL,
   [IsInAct] [bit] NOT NULL CONSTRAINT [DF_NotarialForm_IsInAct] DEFAULT ((0)),
   [IsInEnot] [bit] NOT NULL CONSTRAINT [DF_NotarialForm_IsInEnot] DEFAULT ((0)),
   [UserId] [int] NOT NULL,
   [Comment] [nchar] (1000) COLLATE Cyrillic_General_CI_AS NULL,
   [ModifyDate] [datetime] NOT NULL CONSTRAINT [DF_NotarialForm_ModifyDate] DEFAULT (getdate()),
   [Search] [nchar] (50) COLLATE Cyrillic_General_CI_AS NULL
)
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   ALTER TABLE [dbo].[NotarialForm] ADD CONSTRAINT [PK_NotarialForm] PRIMARY KEY CLUSTERED ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.NotarialForm Table Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.NotarialForm Table'
END
GO


--  
-- Script to Create dbo.NotarialFormHistory in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.NotarialFormHistory Table'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO

SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE [dbo].[NotarialFormHistory] (
   [Id] [int] IDENTITY (1, 1) NOT NULL,
   [Name] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
   [OrderId] [int] NULL,
   [StatusId] [int] NULL,
   [RangeId] [int] NULL,
   [SerialNumberId] [int] NULL,
   [CreateDate] [datetime] NULL,
   [EndDate] [datetime] NULL,
   [IsInAct] [bit] NULL,
   [IsInEnot] [bit] NULL,
   [UserId] [int] NOT NULL,
   [Date] [datetime] NOT NULL CONSTRAINT [DF_NotarialFormHistory_Date] DEFAULT (getdate()),
   [NotarialFormId] [int] NULL,
   [IsDeleted] [bit] NOT NULL CONSTRAINT [DF_NotarialFormHistory_IsDeleted] DEFAULT ((0)),
   [Comment] [nchar] (1000) COLLATE Cyrillic_General_CI_AS NULL
)
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   ALTER TABLE [dbo].[NotarialFormHistory] ADD CONSTRAINT [PK_NotarialFormHistory] PRIMARY KEY CLUSTERED ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.NotarialFormHistory Table Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.NotarialFormHistory Table'
END
GO


--  
-- Script to Create dbo.nrtemp in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.nrtemp Table'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO

SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE [dbo].[nrtemp] (
   [F1] [float] NULL,
   [F2] [nvarchar] (255) COLLATE Cyrillic_General_CI_AS NULL,
   [F3] [nvarchar] (255) COLLATE Cyrillic_General_CI_AS NULL
)
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.nrtemp Table Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.nrtemp Table'
END
GO


--  
-- Script to Create dbo.trg_NotarialForm_IU in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.trg_NotarialForm_IU Trigger'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO

SET NUMERIC_ROUNDABORT OFF
GO


exec('-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[trg_NotarialForm_IU] 
   ON  [dbo].[NotarialForm]
   AFTER INSERT, UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	
	--???????? ??. ???? ???????? ? ?????????? ???????????
	UPDATE O SET O.ModifyDate = GETDATE(), O.Search = CONVERT(nvarchar(50), O.Name) FROM [dbo].[NotarialForm] as O INNER JOIN Inserted as I ON I.Id = O.Id
	
	--????????? ?????? ? ??????? ?????????
	INSERT INTO dbo.[NotarialFormHistory] 
	SELECT 		
		nf.[Name]
		,nf.[OrderId]
		,nf.[StatusId]
		,nf.[RangeId]
		,nf.[SerialNumberId]
		,nf.[CreateDate]
		,nf.[EndDate]
		,nf.[IsInAct]
		,nf.[IsInEnot]
		,nf.[UserId]
		,GETDATE() as [Date]
		,nf.Id as [NotarialFormId]
		,case when exists(select 1 from deleted as d where d.Id = nf.Id) then 1 else 0 end as IsDeleted
		,nf.Comment
	FROM dbo.[NotarialForm] as nf 
	INNER JOIN inserted as I ON NF.Id = I.Id 
	INNER JOIN deleted as D ON D.Id = I.Id

END
')
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.trg_NotarialForm_IU Trigger Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.trg_NotarialForm_IU Trigger'
END
GO


--  
-- Script to Update dbo.trg_Order_IU in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Updating dbo.trg_Order_IU Trigger'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO

SET NUMERIC_ROUNDABORT OFF
GO


exec('-- =============================================
-- Author:		Kolbin Dmitriy
-- Create date: 2009.09.18
-- Description:	Check Order Cost Rows
-- =============================================
ALTER TRIGGER [dbo].[trg_Order_IU] 
   ON  [dbo].[Order]
   AFTER INSERT, UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	--??? ?????????? ???????? ????? ???????+????? ?????? ??????? ?? ????????????
    IF EXISTS
	(
		SELECT 1 FROM [dbo].[Order] as O
		INNER JOIN Inserted as I ON I.Id != O.Id
		 AND ISNULL(I.NotarialRegisterRow,0) > 0 
		 AND I.NotarialRegisterId IS NOT NULL
		 AND I.NotarialRegisterRow = ISNULL(O.NotarialRegisterRow,0)
		 AND I.NotarialRegisterId = O.NotarialRegisterId
		 AND DATEPART(year,O.Created) = DATEPART(year,I.Created)
		INNER JOIN Deleted as D ON I.Id = D.Id
	)
	BEGIN
		RAISERROR(''? ????????? ??????? ??? ?????????? ?????? ? ????? ???????. TRG_NOTARIALREGISTERROW_IU'',16,1)
		ROLLBACK TRAN
	END
			
	--????????? ?????? ? ??????? ?????????
	INSERT INTO dbo.[OrderHistory] 
	SELECT 
		GETDATE() as Date
		, I.StatusId as StatusId
		, I.EditorId as EditorId
		, CASE WHEN NOT EXISTS(SELECT Id FROM Deleted as D WHERE D.LastComment = I.LastComment AND D.Id = I.Id) THEN I.LastComment ELSE '''' END as Comment 
		, I.Operation as Operation		   							
		, I.Id as OrderId
	FROM dbo.[Order] as O 
	INNER JOIN Inserted as I ON O.Id = I.Id AND I.Operation IS NOT NULL AND LEN(I.Operation) > 0

	UPDATE O SET O.Search = ISNULL(O.Search,'''') + '' '' + I.LastComment FROM [dbo].[Order] as O
	INNER JOIN Inserted as I ON I.Id = O.Id AND I.LastComment IS NOT NULL
	

	--???????? ??. ???? ???????? ? ?????????? ???????????
	UPDATE O SET O.Operation = null, O.LastComment = null FROM [dbo].[Order] as O INNER JOIN Inserted as I ON I.Id = O.Id
	
	--???? ???????? ?????????? ???????? ???? ?????????
	UPDATE O SET O.CopyPrice = 0, O.Tax = 0, O.DocPrice = 0, O.IsCashless = 0, O.IsPrepayment = 0, O.IsPaid = 0  FROM [dbo].[Order] as O INNER JOIN Inserted as I ON I.Id = O.Id AND ISNULL(I.IsInternal, 0) = 1

	IF UPDATE(StatusId)
	BEGIN
		UPDATE O SET O.StatusEditorId = I.EditorId FROM [dbo].[Order] as O
		INNER JOIN Inserted as I ON I.Id = O.Id

		UPDATE O SET O.IsPaid = 1 FROM [dbo].[Order] as O
		INNER JOIN Inserted as I ON I.Id = O.Id AND I.IsInternal = 0
		INNER JOIN [dbo].[Status] as S ON I.StatusId = S.Id AND S.Name LIKE ''%??????%''

		UPDATE O SET O.Finished = GETDATE() FROM [dbo].[Order] as O
		INNER JOIN Inserted as I ON I.Id = O.Id
		INNER JOIN [dbo].[Status] as S ON I.StatusId = S.Id AND S.Name LIKE ''%??????%''
		
		UPDATE NF SET NF.EndDate = GETDATE() FROM [dbo].[NotarialForm] as NF
		INNER JOIN Inserted as I ON I.Id = NF.OrderId
		INNER JOIN [dbo].[NotarialFormStatus] NFS ON NFS.Id = NF.StatusId and NFS.Alias like ''%used%''
		INNER JOIN [dbo].[Status] as S ON I.StatusId = S.Id AND S.Name LIKE ''%??????%''
	END

END
')
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.trg_Order_IU Trigger Updated Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Update dbo.trg_Order_IU Trigger'
END
GO


--  
-- Script to Create dbo.NotarialFormRange in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.NotarialFormRange Table'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialFormRange_NotarialFormSerialNumber')
      ALTER TABLE [dbo].[NotarialFormRange] DROP CONSTRAINT [FK_NotarialFormRange_NotarialFormSerialNumber]
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialFormRange_NotarialFormSerialNumber')
      ALTER TABLE [dbo].[NotarialFormRange] ADD CONSTRAINT [FK_NotarialFormRange_NotarialFormSerialNumber] FOREIGN KEY ([SerialNumberId]) REFERENCES [dbo].[NotarialFormSerialNumber] ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.NotarialFormRange Table Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.NotarialFormRange Table'
END
GO


--  
-- Script to Create dbo.NotarialForm in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.NotarialForm Table'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_NotarialFormRange')
      ALTER TABLE [dbo].[NotarialForm] DROP CONSTRAINT [FK_NotarialForm_NotarialFormRange]
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_NotarialFormRange')
      ALTER TABLE [dbo].[NotarialForm] ADD CONSTRAINT [FK_NotarialForm_NotarialFormRange] FOREIGN KEY ([RangeId]) REFERENCES [dbo].[NotarialFormRange] ([Id]) ON DELETE CASCADE
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_NotarialFormSerialNumber')
      ALTER TABLE [dbo].[NotarialForm] DROP CONSTRAINT [FK_NotarialForm_NotarialFormSerialNumber]
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_NotarialFormSerialNumber')
      ALTER TABLE [dbo].[NotarialForm] ADD CONSTRAINT [FK_NotarialForm_NotarialFormSerialNumber] FOREIGN KEY ([SerialNumberId]) REFERENCES [dbo].[NotarialFormSerialNumber] ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_NotarialFormStatus')
      ALTER TABLE [dbo].[NotarialForm] DROP CONSTRAINT [FK_NotarialForm_NotarialFormStatus]
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_NotarialFormStatus')
      ALTER TABLE [dbo].[NotarialForm] ADD CONSTRAINT [FK_NotarialForm_NotarialFormStatus] FOREIGN KEY ([StatusId]) REFERENCES [dbo].[NotarialFormStatus] ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_Order')
      ALTER TABLE [dbo].[NotarialForm] DROP CONSTRAINT [FK_NotarialForm_Order]
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_Order')
      ALTER TABLE [dbo].[NotarialForm] ADD CONSTRAINT [FK_NotarialForm_Order] FOREIGN KEY ([OrderId]) REFERENCES [dbo].[Order] ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_User')
      ALTER TABLE [dbo].[NotarialForm] DROP CONSTRAINT [FK_NotarialForm_User]
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialForm_User')
      ALTER TABLE [dbo].[NotarialForm] ADD CONSTRAINT [FK_NotarialForm_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.NotarialForm Table Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.NotarialForm Table'
END
GO


--  
-- Script to Create dbo.NotarialFormHistory in 192.168.0.4\SQLEXPRESS,1433.tabelliontest 
-- Generated ???????, ??????? 7, 2012, at 03:16 PM 
--  
-- Please backup 192.168.0.4\SQLEXPRESS,1433.tabelliontest before executing this script
--  
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Creating dbo.NotarialFormHistory Table'
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialFormHistory_NotarialForm')
      ALTER TABLE [dbo].[NotarialFormHistory] DROP CONSTRAINT [FK_NotarialFormHistory_NotarialForm]
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialFormHistory_NotarialForm')
      ALTER TABLE [dbo].[NotarialFormHistory] ADD CONSTRAINT [FK_NotarialFormHistory_NotarialForm] FOREIGN KEY ([NotarialFormId]) REFERENCES [dbo].[NotarialForm] ([Id]) ON UPDATE CASCADE ON DELETE CASCADE
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialFormHistory_User')
      ALTER TABLE [dbo].[NotarialFormHistory] DROP CONSTRAINT [FK_NotarialFormHistory_User]
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_NotarialFormHistory_User')
      ALTER TABLE [dbo].[NotarialFormHistory] ADD CONSTRAINT [FK_NotarialFormHistory_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id])
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.NotarialFormHistory Table Added Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Add dbo.NotarialFormHistory Table'
END
GO
